const fs = require('fs').promises;
const path = require('path');
const mysql = require('mysql2/promise');

// 数据库配置
require('dotenv').config({ path: '../.env' });

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'cu_mr_bull_prod',
  charset: 'utf8mb4',
  timezone: '+08:00'
};

// 从前端CouponConfig提取的礼券数据
const coupons = [
  {
    id: 'WELCOME10',
    title: '新用户欢迎券',
    englishTitle: 'Welcome Coupon',
    description: '新用户专享10元优惠券',
    englishDescription: 'Exclusive 10 yuan discount for new users',
    discountType: 'fixed', // fixed: 固定金额, percentage: 百分比
    discountValue: 10.00,
    minimumAmount: 50.00,
    maxDiscount: null, // 对于固定金额券，此字段为null
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 1, // 每个用户可使用次数
    totalLimit: 1000, // 总发放数量
    isActive: true,
    applicableCategories: [], // 空数组表示适用于所有分类
    minimumItems: 1,
    userType: 'new', // new: 新用户, all: 所有用户, vip: VIP用户
    tags: ['welcome', 'new_user']
  },
  {
    id: 'SAVE20',
    title: '满100减20',
    englishTitle: '20 off 100',
    description: '满100元立减20元',
    englishDescription: '20 yuan off when spending 100 yuan or more',
    discountType: 'fixed',
    discountValue: 20.00,
    minimumAmount: 100.00,
    maxDiscount: null,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 3,
    totalLimit: 500,
    isActive: true,
    applicableCategories: [],
    minimumItems: 1,
    userType: 'all',
    tags: ['discount', 'regular']
  },
  {
    id: 'WEEKEND15',
    title: '周末特惠券',
    englishTitle: 'Weekend Special',
    description: '周末专享15%折扣',
    englishDescription: 'Exclusive 15% discount for weekends',
    discountType: 'percentage',
    discountValue: 15.00, // 15%
    minimumAmount: 80.00,
    maxDiscount: 30.00, // 最高优惠30元
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 2,
    totalLimit: 300,
    isActive: true,
    applicableCategories: ['main', 'combo'], // 仅适用于主菜和套餐
    minimumItems: 2,
    userType: 'all',
    tags: ['weekend', 'percentage']
  },
  {
    id: 'COMBO25',
    title: '套餐专享券',
    englishTitle: 'Combo Special',
    description: '套餐类商品专享25元优惠',
    englishDescription: 'Exclusive 25 yuan discount for combo meals',
    discountType: 'fixed',
    discountValue: 25.00,
    minimumAmount: 60.00,
    maxDiscount: null,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 2,
    totalLimit: 200,
    isActive: true,
    applicableCategories: ['combo'],
    minimumItems: 1,
    userType: 'all',
    tags: ['combo', 'special']
  },
  {
    id: 'VIP30',
    title: 'VIP专属券',
    englishTitle: 'VIP Exclusive',
    description: 'VIP会员专享30元优惠券',
    englishDescription: 'Exclusive 30 yuan discount for VIP members',
    discountType: 'fixed',
    discountValue: 30.00,
    minimumAmount: 120.00,
    maxDiscount: null,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 5,
    totalLimit: 100,
    isActive: true,
    applicableCategories: [],
    minimumItems: 1,
    userType: 'vip',
    tags: ['vip', 'exclusive']
  },
  {
    id: 'DRINKS10',
    title: '饮品优惠券',
    englishTitle: 'Drinks Discount',
    description: '饮品类商品8折优惠',
    englishDescription: '20% off on all drinks',
    discountType: 'percentage',
    discountValue: 20.00, // 20% off = 8折
    minimumAmount: 20.00,
    maxDiscount: 15.00,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 3,
    totalLimit: 400,
    isActive: true,
    applicableCategories: ['drinks'],
    minimumItems: 1,
    userType: 'all',
    tags: ['drinks', 'percentage']
  },
  {
    id: 'BIRTHDAY50',
    title: '生日特惠券',
    englishTitle: 'Birthday Special',
    description: '生日月专享50元大额优惠券',
    englishDescription: 'Exclusive 50 yuan discount for birthday month',
    discountType: 'fixed',
    discountValue: 50.00,
    minimumAmount: 200.00,
    maxDiscount: null,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 1,
    totalLimit: 50,
    isActive: true,
    applicableCategories: [],
    minimumItems: 1,
    userType: 'all',
    tags: ['birthday', 'special']
  },
  {
    id: 'STUDENT15',
    title: '学生优惠券',
    englishTitle: 'Student Discount',
    description: '学生专享15元优惠券',
    englishDescription: 'Exclusive 15 yuan discount for students',
    discountType: 'fixed',
    discountValue: 15.00,
    minimumAmount: 60.00,
    maxDiscount: null,
    validFrom: '2024-01-01',
    validUntil: '2024-12-31',
    usageLimit: 2,
    totalLimit: 300,
    isActive: true,
    applicableCategories: [],
    minimumItems: 1,
    userType: 'all',
    tags: ['student', 'discount']
  }
];

// 分类映射（与菜品同步脚本保持一致）
const categoryMapping = {
  'promotion': 'Promotion',
  'recommended': 'Recommended',
  'combo': 'Combo',
  'main': 'Main Dishes',
  'side': 'Side Dishes',
  'drinks': 'Drinks',
  'dessert': 'Desserts'
};

class CouponDataSyncer {
  constructor() {
    this.connection = null;
  }

  async connect() {
    try {
      this.connection = await mysql.createConnection(dbConfig);
      console.log('✅ 数据库连接成功');
    } catch (error) {
      console.error('❌ 数据库连接失败:', error.message);
      throw error;
    }
  }

  async disconnect() {
    if (this.connection) {
      await this.connection.end();
      console.log('✅ 数据库连接已关闭');
    }
  }

  async getCategoryIds(categoryKeys) {
    if (!categoryKeys || categoryKeys.length === 0) {
      return [];
    }

    const categoryNames = categoryKeys.map(key => categoryMapping[key]).filter(Boolean);
    if (categoryNames.length === 0) {
      return [];
    }

    const placeholders = categoryNames.map(() => '?').join(',');
    const [rows] = await this.connection.execute(
      `SELECT id FROM menu_categories WHERE name_en IN (${placeholders})`,
      categoryNames
    );

    return rows.map(row => row.id);
  }

  async syncCoupons() {
    console.log('\n🎫 开始同步礼券数据...');
    
    let successCount = 0;
    let skipCount = 0;
    let errorCount = 0;

    for (const coupon of coupons) {
      try {
        // 检查礼券是否已存在
        const [existing] = await this.connection.execute(
          'SELECT id FROM coupons WHERE code = ?',
          [coupon.id]
        );

        if (existing.length > 0) {
          console.log(`⏭️  礼券已存在: ${coupon.id} - ${coupon.title}`);
          skipCount++;
          continue;
        }

        // 获取适用分类ID
        const categoryIds = await this.getCategoryIds(coupon.applicableCategories);

        // 插入礼券数据
        const [result] = await this.connection.execute(
          `INSERT INTO coupons (
            code, title, title_en, description, description_en, 
            discount_type, discount_value, minimum_amount, max_discount,
            valid_from, valid_until, usage_limit_per_user, total_limit,
            used_count, is_active, applicable_categories, minimum_items,
            user_restrictions, metadata, created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
          [
            coupon.id,
            coupon.title,
            coupon.englishTitle,
            coupon.description,
            coupon.englishDescription,
            coupon.discountType,
            coupon.discountValue,
            coupon.minimumAmount,
            coupon.maxDiscount,
            coupon.validFrom,
            coupon.validUntil,
            coupon.usageLimit,
            coupon.totalLimit,
            0, // used_count 初始为0
            coupon.isActive,
            categoryIds.length > 0 ? JSON.stringify(categoryIds) : null,
            coupon.minimumItems,
            JSON.stringify({ userType: coupon.userType }),
            JSON.stringify({
              tags: coupon.tags,
              originalCategories: coupon.applicableCategories
            })
          ]
        );

        console.log(`✅ 创建礼券: ${coupon.id} - ${coupon.title} (${coupon.discountType === 'fixed' ? '¥' + coupon.discountValue : coupon.discountValue + '%'})`);
        successCount++;
      } catch (error) {
        console.error(`❌ 同步礼券失败 [${coupon.id}]:`, error.message);
        errorCount++;
      }
    }

    console.log(`\n📊 礼券同步完成:`);
    console.log(`   ✅ 成功: ${successCount} 个`);
    console.log(`   ⏭️  跳过: ${skipCount} 个`);
    console.log(`   ❌ 失败: ${errorCount} 个`);
  }

  async run() {
    try {
      console.log('🚀 开始同步前端APP礼券数据到后台数据库...');
      
      await this.connect();
      await this.syncCoupons();
      
      console.log('\n🎉 礼券数据同步完成!');
    } catch (error) {
      console.error('\n💥 同步过程中发生错误:', error.message);
      process.exit(1);
    } finally {
      await this.disconnect();
    }
  }
}

// 运行同步脚本
if (require.main === module) {
  const syncer = new CouponDataSyncer();
  syncer.run().catch(console.error);
}

module.exports = CouponDataSyncer;